﻿using Practice_DotNet.Helper;
using Practice_DotNet.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Practice_DotNet.Operation
{
    internal class LeftJoinOperation
    {
        
        public static async Task<List<LeftJoinModel>> GetAllData()
        {
            List<LeftJoinModel> lsData = new List<LeftJoinModel>();
            string sqlStr = $"""
                SELECT u.account,petName,Gender,Password,CHINESE,ENGLISH,MATH,RECORDDATE FROM userinfo AS u
                LEFT JOIN userscores AS s
                ON u.account = s.account
                """;
            var result = await SQLHelper.SQL_CRUDOnlyRead(sqlStr);
            if (result.Rows.Count>0)
            {
                for (int i = 0; i < result.Rows.Count; i++)
                {
                    lsData.Add(DataRowToLeftJoinModel(result.Rows[i]));
                }
            }
            return lsData;
        }

        private static LeftJoinModel DataRowToLeftJoinModel(DataRow dataRow)
        {
            LeftJoinModel model = new LeftJoinModel();
            
            model.Account = dataRow["ACCOUNT"].ToString()??"";
            model.PetName = dataRow["PetName"].ToString() ?? "";
            model.Gender = Convert.ToInt32(dataRow["Gender"].ToString());
            model.Chinese = Convert.ToSingle(dataRow["Chinese"]);
            model.English = Convert.ToSingle(dataRow["English"]);
            model.Math = Convert.ToSingle(dataRow["Math"]);
            model.RecordDate = Convert.ToDateTime(dataRow["RecordDate"]);
            model.Password = dataRow["Password"].ToString() ?? "";

            return model;
        }
    }
}
